from flask import Flask, request, jsonify
from flask_cors import CORS
import pymysql
import json
from datetime import datetime
from config import Config

app = Flask(__name__)
app.config.from_object(Config)
CORS(app)  # 允许跨域请求


class KindergartenDBTool:
    def __init__(self, config):
        self.db_config = {
            'host': config.DB_HOST,
            'user': config.DB_USER,
            'password': config.DB_PASSWORD,
            'database': config.DB_NAME,
            'port': config.DB_PORT,
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }
        self.connection = None

    def connect(self):
        """连接到数据库"""
        try:
            self.connection = pymysql.connect(**self.db_config)
            return True, "数据库连接成功"
        except Exception as e:
            return False, f"数据库连接失败: {str(e)}"

    def disconnect(self):
        """断开数据库连接"""
        if self.connection:
            self.connection.close()

    def get_parent_by_phone(self, phone):
        """根据手机号查询家长信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_parent 
                    WHERE parent_phone = %s AND is_delete = 0
                """
                cursor.execute(sql, (phone,))
                result = cursor.fetchone()
                return True, result
        except Exception as e:
            return False, f"查询家长信息失败: {str(e)}"

    def get_children_by_parent_id(self, parent_id):
        """根据家长ID查询孩子信息，增加child_status=1的条件"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_child 
                    WHERE parent_id = %s 
                      AND is_delete = 0
                      AND child_status = 1
                """
                cursor.execute(sql, (parent_id,))
                results = cursor.fetchall()
                return True, results
        except Exception as e:
            return False, f"查询孩子信息失败: {str(e)}"

    def get_kindergarten_by_id(self, kindergarten_id):
        """根据幼儿园ID查询幼儿园信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_kindergarten 
                    WHERE kindergarten_id = %s AND is_delete = 0
                """
                cursor.execute(sql, (kindergarten_id,))
                result = cursor.fetchone()
                return True, result
        except Exception as e:
            return False, f"查询幼儿园信息失败: {str(e)}"

    def get_classes_by_id(self, classes_id):
        """根据班级ID查询班级信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_classes 
                    WHERE classes_id = %s AND is_delete = 0
                """
                cursor.execute(sql, (classes_id,))
                result = cursor.fetchone()
                return True, result
        except Exception as e:
            return False, f"查询班级信息失败: {str(e)}"

    def get_classes_by_kindergarten(self, kindergarten_id):
        """根据幼儿园ID查询所有班级信息"""
        try:
            with self.connection.cursor() as cursor:
                sql = """
                    SELECT * FROM kind_tb_classes 
                    WHERE kindergarten_id = %s AND is_delete = 0
                    ORDER BY classes_name
                """
                cursor.execute(sql, (kindergarten_id,))
                results = cursor.fetchall()
                return True, results
        except Exception as e:
            return False, f"查询班级列表失败: {str(e)}"

    def get_orders_by_parent_id(self, parent_id, kindergarten_ids):
        """根据家长ID查询订单信息，增加end_date大于当前日期的限制，并且只显示孩子当前所在幼儿园的数据"""
        try:
            if not kindergarten_ids:
                return True, []

            with self.connection.cursor() as cursor:
                # 构建IN条件的占位符
                placeholders = ','.join(['%s'] * len(kindergarten_ids))
                sql = f"""
                    SELECT * FROM kind_tb_parent_service_order 
                    WHERE parent_id = %s 
                      AND order_status = 2 
                      AND is_delete = 0
                      AND (end_date IS NULL OR end_date > CURDATE())
                      AND kindergarten_id IN ({placeholders})
                """
                # 构建参数列表
                params = [parent_id] + kindergarten_ids
                cursor.execute(sql, params)
                results = cursor.fetchall()
                return True, results
        except Exception as e:
            return False, f"查询订单信息失败: {str(e)}"

    def get_video_services_by_parent_id(self, parent_id, kindergarten_ids):
        """根据家长ID查询视频服务信息，增加end_date大于当前日期的限制，并且只显示孩子当前所在幼儿园的数据"""
        try:
            if not kindergarten_ids:
                return True, []

            with self.connection.cursor() as cursor:
                # 构建IN条件的占位符
                placeholders = ','.join(['%s'] * len(kindergarten_ids))
                sql = f"""
                    SELECT * FROM kind_tb_video_service_parent 
                    WHERE parent_id = %s 
                      AND is_delete = 0
                      AND (end_date IS NULL OR end_date > CURDATE())
                      AND kindergarten_id IN ({placeholders})
                """
                # 构建参数列表
                params = [parent_id] + kindergarten_ids
                cursor.execute(sql, params)
                results = cursor.fetchall()
                return True, results
        except Exception as e:
            return False, f"查询视频服务信息失败: {str(e)}"

    def update_order_classes_id(self, order_id, new_classes_id, child_ids_json):
        """更新订单表中的classes_id，并同时更新kind_tb_child表中的classes_id"""
        try:
            with self.connection.cursor() as cursor:
                # 解析child_ids JSON
                try:
                    child_ids = json.loads(child_ids_json)
                    if not isinstance(child_ids, list):
                        return False, f"订单 {order_id} 的child_ids格式错误"
                except json.JSONDecodeError:
                    return False, f"订单 {order_id} 的child_ids JSON解析失败"

                # 更新订单表的classes_id
                sql_update_order = """
                    UPDATE kind_tb_parent_service_order 
                    SET classes_id = %s, update_time = NOW() 
                    WHERE service_order_id = %s
                """
                cursor.execute(sql_update_order, (new_classes_id, order_id))

                # 更新孩子表的classes_id
                if child_ids:
                    # 构建IN条件的占位符
                    placeholders = ','.join(['%s'] * len(child_ids))
                    sql_update_child = f"""
                        UPDATE kind_tb_child 
                        SET classes_id = %s, update_time = NOW() 
                        WHERE child_id IN ({placeholders})
                    """
                    # 参数：新班级ID + 孩子ID列表
                    params = [new_classes_id] + child_ids
                    cursor.execute(sql_update_child, params)

                self.connection.commit()
                return True, f"订单 {order_id} 的classes_id已更新为 {new_classes_id}，同时更新了 {len(child_ids)} 个孩子的班级信息"
        except Exception as e:
            self.connection.rollback()
            return False, f"更新订单classes_id失败: {str(e)}"

    def update_video_service_classes_id(self, video_service_id, new_classes_id, child_ids_json):
        """更新视频服务表中的classes_id，并同时更新kind_tb_child表中的classes_id"""
        try:
            with self.connection.cursor() as cursor:
                # 解析child_ids JSON
                try:
                    child_ids = json.loads(child_ids_json)
                    if not isinstance(child_ids, list):
                        return False, f"视频服务 {video_service_id} 的child_ids格式错误"
                except json.JSONDecodeError:
                    return False, f"视频服务 {video_service_id} 的child_ids JSON解析失败"

                # 更新视频服务表的classes_id
                sql_update_service = """
                    UPDATE kind_tb_video_service_parent 
                    SET classes_id = %s, update_time = NOW() 
                    WHERE video_service_parent_id = %s
                """
                cursor.execute(sql_update_service, (new_classes_id, video_service_id))

                # 更新孩子表的classes_id
                if child_ids:
                    # 构建IN条件的占位符
                    placeholders = ','.join(['%s'] * len(child_ids))
                    sql_update_child = f"""
                        UPDATE kind_tb_child 
                        SET classes_id = %s, update_time = NOW() 
                        WHERE child_id IN ({placeholders})
                    """
                    # 参数：新班级ID + 孩子ID列表
                    params = [new_classes_id] + child_ids
                    cursor.execute(sql_update_child, params)

                self.connection.commit()
                return True, f"视频服务 {video_service_id} 的classes_id已更新为 {new_classes_id}，同时更新了 {len(child_ids)} 个孩子的班级信息"
        except Exception as e:
            self.connection.rollback()
            return False, f"更新视频服务classes_id失败: {str(e)}"

    def delete_parent_data(self, phone):
        """删除家长用户数据"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                success, parent_info = self.get_parent_by_phone(phone)
                if not success or not parent_info:
                    return False, f"未找到手机号 {phone} 对应的家长信息"

                parent_id = parent_info['parent_id']
                child_id = parent_info.get('child_id')

                # 删除kind_tb_parent中的数据
                sql_delete_parent = """
                    DELETE FROM kind_tb_parent 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_parent, (parent_id,))

                # 如果有child_id，删除kind_tb_child中的数据
                if child_id:
                    sql_delete_child = """
                        DELETE FROM kind_tb_child 
                        WHERE child_id = %s
                    """
                    cursor.execute(sql_delete_child, (child_id,))

                self.connection.commit()
                return True, f"已删除手机号 {phone} 的家长和孩子数据"
        except Exception as e:
            self.connection.rollback()
            return False, f"删除家长数据失败: {str(e)}"

    def delete_order_data(self, phone):
        """删除订单记录数据"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                success, parent_info = self.get_parent_by_phone(phone)
                if not success or not parent_info:
                    return False, f"未找到手机号 {phone} 对应的家长信息"

                parent_id = parent_info['parent_id']

                # 删除kind_tb_parent_service_order中的数据
                sql_delete_orders = """
                    DELETE FROM kind_tb_parent_service_order 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_orders, (parent_id,))

                # 删除kind_tb_video_service_parent中的数据
                sql_delete_video_services = """
                    DELETE FROM kind_tb_video_service_parent 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_delete_video_services, (parent_id,))

                self.connection.commit()
                return True, f"已删除手机号 {phone} 的所有订单和视频服务数据"
        except Exception as e:
            self.connection.rollback()
            return False, f"删除订单数据失败: {str(e)}"

    def clear_wechat_login(self, phone):
        """清除用户登录微信"""
        try:
            with self.connection.cursor() as cursor:
                # 查询家长信息
                success, parent_info = self.get_parent_by_phone(phone)
                if not success or not parent_info:
                    return False, f"未找到手机号 {phone} 对应的家长信息"

                parent_id = parent_info['parent_id']

                # 清空mini_open_id字段
                sql_clear_wechat = """
                    UPDATE kind_tb_parent 
                    SET mini_open_id = NULL, update_time = NOW() 
                    WHERE parent_id = %s
                """
                cursor.execute(sql_clear_wechat, (parent_id,))

                self.connection.commit()
                return True, f"已清除手机号 {phone} 的微信登录信息"
        except Exception as e:
            self.connection.rollback()
            return False, f"清除微信登录信息失败: {str(e)}"


# API 路由
@app.route('/api/query', methods=['POST'])
def api_query():
    data = request.get_json()
    phone = data.get('phone')

    if not phone:
        return jsonify({'success': False, 'message': '请输入手机号'})

    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        # 查询家长信息
        success, parent_info = db_tool.get_parent_by_phone(phone)
        if not success or not parent_info:
            return jsonify({'success': False, 'message': f'未找到手机号 {phone} 对应的家长信息'})

        # 查询孩子信息
        success, children = db_tool.get_children_by_parent_id(parent_info['parent_id'])
        if not success:
            return jsonify({'success': False, 'message': children})

        # 获取孩子当前所在幼儿园的ID列表
        kindergarten_ids = list(set([child['kindergarten_id'] for child in children]))

        # 查询订单信息
        success, orders = db_tool.get_orders_by_parent_id(parent_info['parent_id'], kindergarten_ids)
        if not success:
            return jsonify({'success': False, 'message': orders})

        # 查询视频服务信息
        success, video_services = db_tool.get_video_services_by_parent_id(parent_info['parent_id'], kindergarten_ids)
        if not success:
            return jsonify({'success': False, 'message': video_services})

        # 获取幼儿园和班级名称
        for child in children:
            success, kindergarten_info = db_tool.get_kindergarten_by_id(child['kindergarten_id'])
            if success:
                child['kindergarten_name'] = kindergarten_info.get('kindergarten_name', '未知幼儿园')

            success, classes_info = db_tool.get_classes_by_id(child['classes_id'])
            if success:
                child['classes_name'] = classes_info.get('classes_name', '未知班级')

        for order in orders:
            success, kindergarten_info = db_tool.get_kindergarten_by_id(order['kindergarten_id'])
            if success:
                order['kindergarten_name'] = kindergarten_info.get('kindergarten_name', '未知幼儿园')

            success, classes_info = db_tool.get_classes_by_id(order['classes_id'])
            if success:
                order['classes_name'] = classes_info.get('classes_name', '未知班级')

        for service in video_services:
            success, kindergarten_info = db_tool.get_kindergarten_by_id(service['kindergarten_id'])
            if success:
                service['kindergarten_name'] = kindergarten_info.get('kindergarten_name', '未知幼儿园')

            success, classes_info = db_tool.get_classes_by_id(service['classes_id'])
            if success:
                service['classes_name'] = classes_info.get('classes_name', '未知班级')

        return jsonify({
            'success': True,
            'data': {
                'parent': parent_info,
                'children': children,
                'orders': orders,
                'video_services': video_services
            }
        })
    finally:
        db_tool.disconnect()


@app.route('/api/classes/<kindergarten_id>', methods=['GET'])
def api_get_classes(kindergarten_id):
    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        success, classes_list = db_tool.get_classes_by_kindergarten(kindergarten_id)
        if not success:
            return jsonify({'success': False, 'message': classes_list})

        return jsonify({'success': True, 'data': classes_list})
    finally:
        db_tool.disconnect()


@app.route('/api/modify/order', methods=['POST'])
def api_modify_order():
    data = request.get_json()
    order_id = data.get('order_id')
    new_classes_id = data.get('new_classes_id')
    child_ids_json = data.get('child_ids_json')

    if not all([order_id, new_classes_id, child_ids_json]):
        return jsonify({'success': False, 'message': '参数不完整'})

    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        success, message = db_tool.update_order_classes_id(order_id, new_classes_id, child_ids_json)
        return jsonify({'success': success, 'message': message})
    finally:
        db_tool.disconnect()


@app.route('/api/modify/video', methods=['POST'])
def api_modify_video():
    data = request.get_json()
    video_service_id = data.get('video_service_id')
    new_classes_id = data.get('new_classes_id')
    child_ids_json = data.get('child_ids_json')

    if not all([video_service_id, new_classes_id, child_ids_json]):
        return jsonify({'success': False, 'message': '参数不完整'})

    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        success, message = db_tool.update_video_service_classes_id(video_service_id, new_classes_id, child_ids_json)
        return jsonify({'success': success, 'message': message})
    finally:
        db_tool.disconnect()


@app.route('/api/delete/parent', methods=['POST'])
def api_delete_parent():
    data = request.get_json()
    phone = data.get('phone')

    if not phone:
        return jsonify({'success': False, 'message': '请输入手机号'})

    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        success, message = db_tool.delete_parent_data(phone)
        return jsonify({'success': success, 'message': message})
    finally:
        db_tool.disconnect()


@app.route('/api/delete/orders', methods=['POST'])
def api_delete_orders():
    data = request.get_json()
    phone = data.get('phone')

    if not phone:
        return jsonify({'success': False, 'message': '请输入手机号'})

    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        success, message = db_tool.delete_order_data(phone)
        return jsonify({'success': success, 'message': message})
    finally:
        db_tool.disconnect()


@app.route('/api/wechat/clear', methods=['POST'])
def api_clear_wechat():
    data = request.get_json()
    phone = data.get('phone')

    if not phone:
        return jsonify({'success': False, 'message': '请输入手机号'})

    db_tool = KindergartenDBTool(Config)
    success, message = db_tool.connect()
    if not success:
        return jsonify({'success': False, 'message': message})

    try:
        success, message = db_tool.clear_wechat_login(phone)
        return jsonify({'success': success, 'message': message})
    finally:
        db_tool.disconnect()


if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)